﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using DTO;

namespace DAL
{
    public partial class GetData
    {
        CRMEntities db = new CRMEntities();

        public List<CallAgentEntity> GetListCAandTask(int RegionID)
        {
            db = new CRMEntities();
            List<CallAgentEntity> list = new List<CallAgentEntity>();
            foreach (User user in db.Users.Where(a => a.Role == 3 && a.Region_Code == RegionID))
            {
                CallAgentEntity ca = GetCallByID(user.User_ID);
                ca.TotalCalls = ca.TotalC1 + ca.TotalC2 + ca.TotalE1 + ca.TotalE2 + ca.TotalKPI;
                ca.ID = user.User_ID;
                ca.Name = user.LastName + " " + user.FirstName;
                ca.RegionCode = user.Region_Code;
                list.Add(ca);
            }

            return list;
        }
    

        public CallAgentEntity GetCallByID(int _ID)
        {
            db = new CRMEntities();
            var caData = db.DividingWorkings.Where(a => a.User_ID == _ID);

            CallAgentEntity ca = new CallAgentEntity();
            ca.TotalC1 = caData.Where(b => b.CallStatus_ID == 1).Count();
            ca.TotalC2 = caData.Where(b => b.CallStatus_ID == 2).Count();
            ca.TotalE1 = caData.Where(b => b.CallStatus_ID == 3).Count();
            ca.TotalE2 = caData.Where(b => b.CallStatus_ID == 4).Count();
            ca.TotalKPI = caData.Where(b => b.CallStatus_ID == 5).Count();

            return ca;
        }

        public List<UserEntity> GetAllUser()
        {
            db = new CRMEntities();            
            var query = (from user in db.Users
                         select new UserEntity
                         {
                             UserID = user.User_ID,
                             FirstName = user.FirstName,
                             LastName = user.LastName,
                             TelephoneNumber = user.TelephoneNumber,
                             Account = user.Account,
                             Password = user.Password,
                             Region_Code = user.Region_Code,
                             Role = (int)user.Role,
                             Disable = user.IsDisable
                         }).ToList();

            return query;
        }

        public User FindUser(string Account, string Pass, ref string message)
        {
            db = new CRMEntities();
            User query = new User();
            try
            {
                try
                {
                    query = db.Users.Where(a => a.Account == Account && a.Password == Pass).SingleOrDefault();
                    if (query != null)
                    {
                        if (query.IsDisable == true)
                        {
                            message = "Tài khoản đã bị khóa\nLiên hệ người quản trị để biết thêm chi tiết";
                            return null;
                        }
                        else
                        {
                            UserInfoEntity info = new UserInfoEntity();
                            info.LastName = query.LastName;
                            info.FirstName = query.FirstName;
                            info.UserID = query.User_ID;
                            info.TelephoneNumber = query.TelephoneNumber.Trim();
                            info.Account = query.Account;
                            info.Password = query.Password;
                            info.Role = (int)query.Role;
                            return query; 
                        }                        
                    }                    
                    else
                    {
                        message = "Tài khoản không tồn tại";
                        return null;
                    }
                }
                catch (Exception ex)
                {
                    message = ex.Message;
                    return null;
                }
                finally
                {
                    db.Dispose();
                }
            }
            catch
            {
                throw;
            }
        }

        public bool IsUserExisted(UserEntity user)
        {
            db = new CRMEntities();
            var query = (from u in db.Users
                         where u.Account == user.Account && u.User_ID != user.UserID                         
                         select u).ToList();
            if (query.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public List<DTO.RegionEntity> LoadRegionList()
        {
            db = new CRMEntities();
            return (from dt in db.Regions
                    where dt.RegionLevel != 0
                    select new DTO.RegionEntity
                    {
                        Region_Code = dt.Region_Code,
                        RegionName = dt.RegionName,
                        Parent_Code = (int)dt.Parent_Code,
                        RegionLevel = dt.RegionLevel,
                        RegionType = dt.RegionType,
                        Ordinal = dt.Ordinal
                    }).ToList();
        }  

        public List<SourceEntity> loadSource(DateTime startdate, DateTime enddate)
        {
            try
            {
                CRMEntities db = new CRMEntities();
                List<SourceEntity> listSources = new List<SourceEntity>();
                int statusid = SharedData.StatusList.Where(b => b.Status_Code == "POT").SingleOrDefault().Status_ID;
                var query = from so in db.Sources.Where(a => a.ImportDate >= startdate && a.ImportDate <= enddate)
                            select so;

                foreach (var item in query.ToList())
                {
                    SourceEntity se = new SourceEntity();
                    se.Source_ID = item.Source_ID;
                    se.HCPDate = item.SourceName + " " + item.ImportDate.ToString("dd/MM/yy") + " " + item.ImportDate.ToShortTimeString();
                    listSources.Add(se);
                }

                return listSources;
            }
            catch
            { throw; }
            finally { db = null; }


        }
        

        public List<TaskListEntity> LoadTaskList(int UserID)
        {
            db = new CRMEntities();
            var query = from d in db.DividingWorkings.Where(a => a.User_ID == UserID)
                        join cu in db.Customers on d.Customer_ID equals cu.Customer_ID
                        join us in db.Users on d.User_ID equals us.User_ID
                        join call in db.CallStatus on d.CallStatus_ID equals call.ID
                        orderby d.CallStatus_ID ascending
                        select new TaskListEntity 
                        {
                            CallStatus = call.Name,
                            CallStatusID = d.CallStatus_ID,
                            CustomerName = cu.LastName + " " + cu.FirstName,
                            CellPhoneNumber = cu.CellPhoneNumber,
                            HomePhoneNumber = cu.HomePhoneNumber,
                            OfficePhoneNumber = cu.OfficePhoneNumber,
                            CustomerID = cu.Customer_ID,
                            UserID = us.User_ID
                        };  
            return query.ToList();
        }

        public List<ResultStatusEntity> getResultStatusGroup(int CallTypeID)
        {
            db = new CRMEntities();
            List<ResultStatusEntity> list = new List<ResultStatusEntity>();

            if (CallTypeID == 2)
            {
                var query = SharedData.ResultStatusList.Where(a => a.Result_Level == 1).ToList();
                list = query;
            }

            if (CallTypeID == 3)
            {
                var query = SharedData.ResultStatusList.Where(a => a.Result_Level == 1 && a.Result_Code != "Fo").ToList();
                list = query;
            }

            if (CallTypeID == 5)
            {
                var query = SharedData.ResultStatusList.Where(a => a.Result_Level == 1 && a.Result_Code != "User").ToList();
                list = query;
            }
            else if (CallTypeID == 6)
            {
                var query = SharedData.ResultStatusList.Where(a => a.Result_Level == 1 && a.Result_Code != "Fo" && a.Result_Code != "UP").ToList();
                list = query;
            }

            //var query = SharedData.ResultStatusList.Where(a => a.Result_Level == 1).ToList();
           // list = query;
            return list;
        }
        public List<SampleEntity> getSampleSachet()
        {
            db = new CRMEntities();
            var query = (from r in db.Samples
                         where r.Parent_Code == 1 //(SharedData.SampleList.Where(a => a.Sample_Code == "SA").SingleOrDefault().Sample_ID)
                        select new SampleEntity  
                        {
                            Sample_ID = r.ID,
                            Sample_Code = r.Sample_Code,
                            SampleName = r.SampleName,
                            Sample_Level = r.SampleLevel,
                            Parent_Code = r.Parent_Code,
                            Description = r.Description
                        }).ToList();
            return query;
        }

        public List<SampleEntity> getSampleBooklet()
        {
            db = new CRMEntities();
            var query = from r in db.Samples
                        where r.Parent_Code == 2 //(SharedData.SampleList.Where(a => a.Sample_Code == "BO").SingleOrDefault().Sample_ID)
                        select new SampleEntity
                        {
                            Sample_ID = r.ID,
                            Sample_Code = r.Sample_Code,
                            SampleName = r.SampleName,
                            Description = r.Description,
                            Sample_Level = r.SampleLevel,
                            Parent_Code = r.Parent_Code,
                        };
            return query.ToList();
        }

        // Lấy danh sách Sample đã chọn từ khách hàng
        public List<SampleDetailEntity> getListSample(long CustomerID)
        {
            db = new CRMEntities();
            var query = from s in db.SampleDetails
                        join cd in db.OutBoundDetails on s.CallDetail_ID equals cd.ID
                        where cd.Customer_ID == CustomerID
                        select new DTO.SampleDetailEntity
                            {
                                SampleDetail_ID = s.ID,
                                DateCheck = (DateTime)s.CheckedDate,
                                DateReceive = (DateTime)s.ReceivedDate,
                                SendStatus = (bool)s.SendStatus
                            };
            return query.ToList();
        }

        // Lấy lịch sử thông tin cập nhật
        public List<HistoricalDataEntity> getHistoryInformation(long CustomerID)
        {
            db = new CRMEntities();
            var query = from h in db.HistoricalDatas
                        join s in db.Sources on h.Source_ID equals s.Source_ID
                        join r in db.Regions on h.Ward_Code & h.Province_Code & h.District_Code equals r.Region_Code
                        join u in db.Users on h.LastUpdatedBy equals u.User_ID
                        where h.Customer_ID == CustomerID
                        select new DTO.HistoricalDataEntity
                        {
                            HistoricalData_ID = (long)h.ID,
                            Source_ID = (int)h.Source_ID,
                            SourceName = s.SourceName,
                            CollectedDate = h.CollectedDate,
                            Collector_ID = h.Collector_ID,
                            ClinicInfo = h.ClinicInfo,
                            Customer_ID = (long)h.Customer_ID,
                            FirstName = h.FirstName,
                            LastName = h.LastName,
                            Note = h.Note,
                            EDD = h.EDD,
                            IDNumber = h.IDNumber,
                            DateOfBirth = h.DateOfBirth,
                            HomeNumber = h.HomeNumber,
                            Substreet = h.Substreet,
                            Street = h.Street,
                            Ward_Code = h.Ward_Code,
                            District_Code = h.District_Code,
                            Province_Code = h.Province_Code,
                            CellPhoneNumber = h.CellPhoneNumber,
                            HomePhoneNumber = h.HomePhoneNumber,
                            OfficePhoneNumber = h.OfficePhoneNumber,
                            CurrentBrandName = h.CurrentBrandName,
                            PreviousBrandName = h.PreviousBrandName,
                            LastUpdatedBy = (int)h.LastUpdatedBy,
                            LastUpdatedByName = u.LastName + " " + u.FirstName,
                            LastUpdatedDate = h.LastUpdatedDate
                        };
            return query.ToList();
        }

        public List<StatusEntity> LoadStatusList()
        {
            db = new CRMEntities();
            var query = (from s in db.Status
                         orderby s.StatusLevel
                         select new StatusEntity
                         {
                             Status_ID = s.ID,
                             Status_Code = s.Status_Code,
                             StatusName = s.StatusName,
                             Decription = s.Description,
                             StatusLevel = s.StatusLevel,
                             Parent_Code = s.Parent_Code
                         }).ToList();
            return query;
        }

        #region System_Management
        #region User_Management
        public List<PermissionEntity> LoadListPermission()
        {
            db = new CRMEntities();
            var query = (from p in db.Permissions
                        select new DTO.PermissionEntity
                        {
                            Permission_ID = p.Permission_ID,
                            PermissionName = p.PermissionName,
                            VisibleName = p.VisibleName,
                            Status = false,
                            PermissionGroup_ID = p.PermissionGroup_ID
                        }).ToList();
            foreach (PermissionEntity item in query)
            {
                if (item.PermissionGroup_ID == null)
                {
                    item.PermissionGroup_ID = 0;
                }
            }
            return query;
        }

        public List<PermitDetailEntity> LoadPermitDetail(int User_ID)
        {
            db = new CRMEntities();
            var query = from p in db.PermitDetails
                        where p.User_ID == User_ID
                        select new DTO.PermitDetailEntity
                        {
                            User_ID = p.User_ID,
                            Permission_ID = p.Permission_ID,                          
                            Status = (bool)p.Status
                        };
            return query.ToList();
        }

        public List<PermitDetailEntity> LoadUserPermit(int userid)
        {
            db = new CRMEntities();
            var query = (from p in db.PermitDetails
                         join a in db.Permissions on p.Permission_ID equals a.Permission_ID
                            where p.User_ID == userid
                        select new DTO.PermitDetailEntity
                        {
                            User_ID = p.User_ID,
                            Permission_ID = p.Permission_ID,
                            PermissionName = a.VisibleName,
                            Status = (bool)p.Status
                        }).ToList();
            return query;
        }
        #endregion

        #region Catalog_Management
        #region Catalog_ResultStatus
        public List<ResultStatusEntity> LoadResultStatusList()
        {
            db = new CRMEntities();
            var query = from r in db.ResultStatus
                        select new DTO.ResultStatusEntity
                        {
                            ResultID = r.ID,
                            Result_Code = r.Result_Code,
                            Result_Level = r.Result_Level,
                            ResultName = r.ResultName,
                            Description = r.Description,
                            Parent_Code = (int)r.Parent_Code,
                            IsDisable = r.IsDisable
                        };
            return query.ToList();
        }
        #endregion

        #region Catalog_Collector
        public List<CollectorEntity> LoadListCollector()
        {
            db = new CRMEntities();
            var query = from c in db.Collectors
                        select new DTO.CollectorEntity
                        {
                            Collector_ID = c.ID,
                            Collector_Code = c.Collector_Code,
                            FullName = c.FullName,
                            Region_ID = c.Region_ID,
                        };
            return query.ToList();
        }

        public CollectorEntity LoadCollector(int Collector_ID)
        {
            db = new CRMEntities();
            var query = (from c in db.Collectors.Where(a => a.ID == Collector_ID)
                         select new CollectorEntity
                         {
                             Collector_ID = c.ID,
                             FullName = c.FullName,
                             Region_ID = c.Region_ID
                         }).SingleOrDefault();
            return query;
        }
        #endregion
        #endregion

        #region Setting
        public void LoadCallSetting()
        { 
            db = new CRMEntities();
            System.Collections.ArrayList setting = new System.Collections.ArrayList();
            var query = (from call in db.SettingCalls
                         orderby call.ID ascending
                        select new
                        {
                            ID = call.ID,
                            C1 = call.C1,
                            E1 = call.E1,
                            E2 = call.E2,
                        }).ToList();
            SharedData.C2Policy = query[0].C1.ToString();
            SharedData.E1Policy = query[0].E1.ToString();
            SharedData.E2Policy = query[0].E2.ToString();
        }


        #endregion

        #endregion

        public List<BrandEntity> LoadListBrand()
        {
            db = new CRMEntities();
            var query = (from b in db.Brands
                         orderby b.ID
                         select new BrandEntity
                         {
                             Brand_ID = b.ID,
                             Brand_Code = b.Brand_Code,
                             BrandName = b.BrandName,
                             BrandLevel = b.BrandLevel,
                             Description = b.Description,
                             Parent_Code = b.Parent_Code,
                             IsCustomer = b.IsCustomer,
                             IsDisable = b.IsDisable
                         }).ToList();
            return query;
        }

        public List<SampleEntity> LoadListSample()
        {
            db = new CRMEntities();
            var query = (from sa in db.Samples
                         orderby sa.ID
                         select new SampleEntity
                         {
                             Sample_ID = sa.ID,
                             Sample_Code = sa.Sample_Code,
                             SampleName = sa.SampleName,
                             Sample_Level = sa.SampleLevel,
                             Description = sa.Description,
                             Parent_Code = sa.Parent_Code
                         }).ToList();
            return query;
        }
        
        public List<RoleEntity> LoadRole()
        {
            db = new CRMEntities();
            var query = (from r in db.Roles
                         orderby r.ID
                         select new RoleEntity
                         {
                             ID = r.ID,
                             Role_Name = r.Role_Name
                         }).ToList();
            return query;  
        }

        public List<IBDetailEntity> LoadAllIBDetail()
        {
            db = new CRMEntities();
            var query = (from IB in db.InBoundDetails
                         select new IBDetailEntity
                           {
                             DateOfComplain = (DateTime)IB.DateOfComplain,
                               SourceReciving = IB.SourceReciving,
                               Customer_ID = IB.Customer_ID,
                               NSX = (DateTime)IB.NXS,
                               HSD = (DateTime)IB.HSD,
                               BrandNameID = IB.BrandName_ID,
                               ReasonComplain = IB.ReasonComplain,
                               Status = IB.Status,
                               CRMResponse = IB.CRMResponse,
                               SupplyResponse = IB.SupplyResponse,
                               FactoryResponse = IB.FactoryResponse,
                               ProductExchange = IB.ProductExchange,
                               GiftProduct = IB.GiftProduct,
                             DatetoFeedBack = (DateTime)IB.DatetoFeedBack
                           }).ToList();               
 
            return query;
        }

        public List<IBDetailEntity> LoadIBDetailByCustomerID(long _customerID)
        {
            db = new CRMEntities();
            var query = (from IB in db.InBoundDetails
                         where IB.Customer_ID == _customerID
                         select new IBDetailEntity
                         {
                             DateOfComplain = (DateTime)IB.DateOfComplain,
                             SourceReciving = IB.SourceReciving,
                             Customer_ID = IB.Customer_ID,
                             NSX = (DateTime)IB.NXS,
                             HSD = (DateTime)IB.HSD,
                             BrandNameID = IB.BrandName_ID,
                             ReasonComplain = IB.ReasonComplain,
                             Status = IB.Status,
                             CRMResponse = IB.CRMResponse,
                             SupplyResponse = IB.SupplyResponse,
                             FactoryResponse = IB.FactoryResponse,
                             ProductExchange = IB.ProductExchange,
                             GiftProduct = IB.GiftProduct,
                             DatetoFeedBack = (DateTime)IB.DatetoFeedBack
                         }).ToList();

            return query;
        }

    }
}
